package com.globalwave.common.db;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.persistence.Column;



import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.impl.LogFactoryImpl;
import org.hibernate.SessionFactory;
import org.hibernate.dialect.MySQL5Dialect;
import org.hibernate.dialect.Oracle9Dialect;

import com.globalwave.base.BaseSO;
import com.globalwave.common.ArrayOrderList;
import com.globalwave.common.ArrayPageList;
import com.globalwave.common.C;
import com.globalwave.common.cache.CodeHelper;
import com.globalwave.common.db.gen.SQLGenerator;
import com.globalwave.common.db.gen.SqlGeneratorAdapter;
import com.globalwave.common.exception.BusinessException;

public class CopyOfSimpleJDBCDAO {
	
    private Log log = LogFactoryImpl.getLog(this.getClass()) ;
    private SqlGeneratorAdapter generatorAdapter = null;//new SqlGeneratorAdapter(this) ;
    private Connection connection ;
    
    private static Map<Class<?>, Integer> typesMap = new HashMap<Class<?>, Integer>() ;
    static {
    	typesMap.put(Short.class, Types.SMALLINT) ;
    	typesMap.put(Integer.class, Types.INTEGER) ;
    	typesMap.put(Long.class, Types.BIGINT) ;
    }
    
    private SessionFactory sessionFactory ;
    
    public void setSessionFactory(SessionFactory sessionFactory) {
        this.sessionFactory = sessionFactory ;   
    }
    
    public Connection getConnection() {
    	//if (connection == null) {
    	if (sessionFactory != null) {
    		connection = sessionFactory.getCurrentSession().connection() ;
    	}
    	//}
        return connection; 

    }
    
    public void setConnection(Connection connection) {
    	this.connection = connection ; 
    }


    public Object create(Object object) throws Exception {
        return this.create(object, false) ;
    }


    public void createAll(List<?> objects) throws Exception {
    	if (objects == null || objects.size() == 0) {
    		return ;
    	}
        for (Object obj:objects) {
        	create(obj) ;// TODO 需要改为批量操作，提高效率！！
        }
    }


    public Object create(Object object, final boolean isFileObject) {
    	
    	PreparedStatement statement = null ;
    	SQLGenerator.INSERT create = null ;
    	SQLGenerator generator = null ;
    	int i = 0 ;
    	
    	try {
    		generator = generatorAdapter.getInstance(object) ;
	        create = generator.getInsert() ;       
	        
	        statement = this.getConnection().prepareStatement(create.sql.toString()) ;
	        
	        for(; i < create.params.size() ; i ++) {
	            final Object fieldObj = create.params.get(i) ;
	            if (isFileObject && fieldObj instanceof File){
	                final File file = (File)fieldObj ;
	                int len = (int) file.length();
	                InputStream fis = new FileInputStream(file);
	                statement.setBinaryStream(i + 1,fis,len);
	            }  else {	            	
	                statement.setObject(i + 1, fieldObj) ;//
	            }
	        }
	        
	        statement.executeUpdate() ;
	        
    	} catch (Exception e) {
    		if (create != null) {
    		    //log(create.sql.toString(), create.params, true) ;
    		    
    		    if ((i + 1)!= create.params.size()) {
    		    	log.error("Exception is throwed when setting object, index is:" + i) ;
    		    }
    		}
			log.error(e, e) ;
    		throw new BusinessException(e, "1000") ;
    	} finally{
    		if (statement != null) {
    		    try {
					statement.close() ;
				} catch (SQLException e) {
					log.error(e, e) ;
				}
    		}
    	}
        return object;
    }





    public int update(Object object) throws Exception {
    	SQLGenerator generator = generatorAdapter.getInstance(object) ;
        return this.update(generator, generator.getIdWhere()) ;
        
    } 
    
    public int update(Object object, Object criterion) throws Exception {
    	SQLGenerator generator = generatorAdapter.getInstance(criterion) ;
    	SQLGenerator.WHERE where = generator.getWhere() ;
    	
    	generator = generatorAdapter.getInstance(object) ;
    	
        return this.update(generator, where) ;
    } 
    
    private int update(SQLGenerator generator, SQLGenerator.WHERE where) throws Exception {
    	PreparedStatement statement = null ;
    	SQLGenerator.UPDATE update = null ;
    	int i = 0 ;
    	
    	try {    				
        	update = generator.getUpdate() ;
        	
            StringBuffer sql = update.sql.append(where.sql) ;
        	statement = this.getConnection().prepareStatement(sql.toString()) ;
	        
	        for( ; i < update.params.size() ; i ++) {
	            statement.setObject(i + 1, update.params.get(i)) ;// 索引从1开始
	        }
	        
	        for( int j = 0; j < where.params.size() ; j ++) {
	            statement.setObject(j + i + 1 , where.params.get(j)) ;// 索引从1开始
	        }
	        
	        int result = statement.executeUpdate() ;

            return result ;
            
    	} catch (Exception e) {
    		if (update != null) {
    		    //generator.log(update.sql.toString(), update.params, true) ;
    		    
    		    if ((i + 1)!= update.params.size()) {
    		    	log.error("Exception is throwed when setting object, index is:" + i) ;
    		    }
    		}
			log.error(e, e) ;
    		throw new BusinessException(e, "1000") ;
    	} finally{
    		if (statement != null) {
    		    try {
					statement.close() ;
				} catch (SQLException e) {
					log.error(e, e) ;
				}
    		}
    	}
    }
    
    public int update(
    		String tableName, 
    		String[] updateFields, Object[] updateValues, 
    		String[] whereFields, Object[] whereValues) throws Exception {
    	


        Map<String, Object> updateMap = new HashMap<String, Object>() ;
        for (int i = 0 ; i < updateFields.length ; i ++) {
            updateMap.put(updateFields[i], updateValues[i]) ;
        }
        updateMap.put("$TABLE", tableName) ;
        
        Map<String, Object> whereMap = new HashMap<String, Object>() ;
        for (int i = 0 ; i < whereFields.length ; i ++) {
        	whereMap.put(whereFields[i], whereValues[i]) ;
        }
        
        return update(updateMap, whereMap);
    }

    public int delete(Object object) throws Exception {
        //SQLGenerator generator = new SQLGenerator() ;
    	SQLGenerator generator = generatorAdapter.getInstance(object) ;
        //SQLGenerator.WHERE where = generator.getIdWhere() ;
        //String sql = " DELETE FROM " + generator.getTableName() + where.sql ;
        //return delete(sql, where);
        return delete(generator.getTableName(object.getClass()), generator, true);
        
    }

    public int delete(Class<?> clazz, Object criterion) throws Exception {
    	SQLGenerator generator = generatorAdapter.getInstance(criterion) ;
        //SQLGenerator.WHERE where = generator.getWhere() ;
        //String sql = " DELETE FROM " + generator.getTableName(clazz) + where.sql ;
        
        return delete(generator.getTableName(clazz), generator, false);
        
    }

    public int delete(String tableName, Object criterion) throws Exception {
    	SQLGenerator generator = generatorAdapter.getInstance(criterion) ;
        //SQLGenerator.WHERE where = generator.getWhere() ;
        //String sql = " DELETE FROM " + tableName + where.sql ;
        
        return delete(tableName, generator, false);
        
    }

    private int delete(String tableName, SQLGenerator generator, boolean isById) throws SQLException {
    	
    	PreparedStatement statement = null ;
    	SQLGenerator.DELETE delete = null ;
    	int i = 0 ;
    	
    	try {
    		delete = generator.getDelete(tableName, isById) ;
    		
	        statement = this.getConnection().prepareStatement(delete.sql.toString()) ;        
	        
	        for(; i < delete.params.size() ; i ++) {
	            statement.setObject(i + 1, delete.params.get(i)) ;// 索引从1开始
	        }
	        
	        int result = statement.executeUpdate() ;
	    
	
	        return result ;
	        
		} catch (Exception e) {
			if (delete != null) {
			    //generator.log(delete.sql.toString(), delete.params, true) ;
			    
			    if ((i + 1)!= delete.params.size()) {
			    	log.error("Exception is throwed when setting object, index is:" + i) ;
			    }
			}
			log.error(e, e) ;
			throw new BusinessException(e, "1000") ;
		} finally{
			if (statement != null) {
			    try {
					statement.close() ;
				} catch (SQLException e) {
					log.error(e, e) ;
				}
			}
		}
      
    }   
    
    /*
    public List query(String sql,Class clazz, Object whereV, int pageNo, int pageSize) throws Exception {
        return query(sql,clazz,whereV);
    }*/
    /**
     * 根据对象ID，查找对象
     */
    public Object find(Object object) throws Exception {
        
        ArrayPageList<?> list = query(object.getClass(), object, null, null, null) ;
        if (list.size() > 0) {
            return list.get(0) ;
        } else {
            return null ;
        }
    }
    
    public Object get(Object object) throws Exception {

        //SQLGenerator generator = new SQLGenerator() ;
    	SQLGenerator generator = generatorAdapter.getInstance(object) ;
        SQLGenerator.WHERE where = generator.getIdWhere() ;
        String sql = " Select * FROM " + generator.getTableName() + where.sql ;
        
        ArrayPageList<?> list = query(sql, where.params, object.getClass(), null, null, false);
        if (list.size() > 0) {
            return list.get(0) ;
        } else {
            return null ;
        }
    }

    public ArrayPageList<?> query(Class<?> clazz, BaseSO so,ArrayOrderList orderList) throws Exception {
    	
    	int index = so.getPageIndex() == null?-1:so.getPageIndex();
    	int size = so.getPageSize() == null?0:so.getPageSize() ;
    	
    	so.setPageIndex(null) ;
    	so.setPageSize(null) ;
    	
    	return query(clazz, so, orderList, index, size) ;
    }
    
    public ArrayPageList<?> query(Class<?> clazz, Object criterion,ArrayOrderList orderList) throws Exception {
        return query(clazz, criterion, orderList, null,null) ;
    }
    
    public ArrayPageList<?> query(Class<?> clazz, Object criterion,ArrayOrderList orderList, Integer pageIndex, Integer pageSize) throws Exception {
        String sql = "Select * from " + new SQLGenerator().getTableName(clazz);
        return query(sql, clazz, criterion, orderList, pageIndex,pageSize) ;
    }
    
    public ArrayPageList<?> query(String sql,Class<?> clazz, Object criterion,ArrayOrderList orderList) throws Exception {
        return query(sql, clazz, criterion, orderList, null, null) ;    
    }
    
    public ArrayPageList<?> query(
            String sql,
            Class<?> clazz, 
            Object criterion,
            ArrayOrderList orderList, 
            Integer pageIndex, 
            Integer pageSize) 
    throws Exception {
        
       return query(sql, clazz, criterion, orderList, pageIndex, pageSize, false) ; 
    }
    
    public ArrayPageList<?> query(String sql,Class<?> clazz, Object criterion,ArrayOrderList orderList, Integer pageIndex, Integer pageSize, final boolean isFileObject) throws Exception {

    	SQLGenerator generator = generatorAdapter.getInstance(criterion) ;
        SQLGenerator.WHERE where = generator.getWhere() ;
        
        sql = String.format("select * from (%s) a",sql) ;
        
        sql += where.sql ;
        
        if (orderList != null) {
            sql += orderList.toOrderString() ;    
        }
        
        return query(sql, where.params, clazz, pageIndex, pageSize, isFileObject) ;        
    }
    
    
    private ArrayPageList<?> query(String sql, final List<Object> params, Class<?> clazz, 
    		Integer pageIndex, Integer pageSize, final boolean isFileObject) throws Exception {

        int fileFieldIndex = -1 ;
    	boolean clazzIsMap = (clazz.equals(HashMap.class)) ;
    	
        ArrayPageList<Object> result = new ArrayPageList<Object>() ;

        if (pageIndex != null && pageSize != null && pageIndex >= -1 && pageSize != Integer.MAX_VALUE) {
            result.setPageIndex(pageIndex) ; 
            if (pageIndex != -1) {
                result.setTotal(getTotalCount(sql, params)) ;
            }
            
            // 支持分页
            sql = getLimitString(sql, params, (pageIndex == -1?1:pageIndex), pageSize) ;

            //where.params.add((pageIndex-1) * pageSize) ;
            //where.params.add(pageSize) ;
        }
        
        logSQL(sql, params) ;
    	
        //log.debug("query sql-->" + sql) ;
        PreparedStatement statement = this.getConnection().prepareStatement(sql) ;

        //log.debug("params-->" ) ;
        for(int i = 0 ; i < params.size() ; i ++) {
            //log.debug(">" + params.get(i)) ;
            statement.setObject(i + 1, params.get(i)) ;// 索引从1开始
        }
        //log.debug("<--params") ;
        
        ResultSet rs = statement.executeQuery() ;

        List<String> columns = new ArrayList<String> () ;
        
        ResultSetMetaData meta = rs.getMetaData();
        int numberOfColumns = meta.getColumnCount();

        String[] properties = new String[numberOfColumns];
        boolean[] timestampIndexs = new boolean[numberOfColumns];
        int[] types = new int[numberOfColumns];

        for (int i = 1; i <= numberOfColumns; i++) {
        	final String columnName = meta.getColumnName(i).toLowerCase() ;
            columns.add(columnName) ;
            
            if (clazzIsMap) {
                properties[i - 1] = columnName ;
            }
            
            types[i - 1] = getType(meta, i) ;

        	timestampIndexs[i - 1] = (meta.getColumnType(i) == Types.TIMESTAMP) ;
        }
        
        if (!clazzIsMap) {
        	
	        for (Field field : clazz.getDeclaredFields()) {
	            String propertie = field.getName() ;
	
	            Column column = field.getAnnotation(Column.class) ;
	
	            int index ;
	            if (column != null) {
	                index = columns.indexOf(column.name()) ;
	            } else {
	                index = columns.indexOf(propertie) ;                
	            }
	            
	            if (index >= 0) {// 属性有对应的列
	                properties[index] = propertie ;
	                
	                Integer t = typesMap.get(field.getType()) ;
	                
	                if (t != null) {
	                	types[index] = t ;
	                }
	                
	                if (isFileObject) {
	                    if (field.getType().equals(java.io.File.class)) {
	                        fileFieldIndex = index ;
	                    }
	                }
	            }
	        }
        }
        
        while( rs.next() ) {
            Object object = clazz.newInstance() ;
            Map<String, Object> mapObject = clazzIsMap ? (Map<String, Object>) object : null ;
            
            for (int i = 0 ; i < properties.length ; i ++) {
                String property = properties[i] ;
                int type = types[i] ;
                
                if (property != null) {
                    if (fileFieldIndex == i) {// 对blob特殊处理
                        File f = new File(C.TEMP_DIR + "/"  + System.currentTimeMillis() + Math.random() + ".tmp") ;
                        FileOutputStream fos = new FileOutputStream(f) ;
                        InputStream is = rs.getBinaryStream(i + 1) ;

                        byte[] buf = new byte[1024] ;
                        while (is.read(buf) > 0) {
                            fos.write(buf) ;                            
                        }
                        fos.close() ;
                        
                        if (clazzIsMap) {
                        	mapObject.put(property, f) ;
                        } else {
                            BeanUtils.setProperty(object, property, f) ;
                        } 
                    } else {
                        if (clazzIsMap) {
                        	mapObject.put(property, getObject(rs, i, type)) ;
                        } else {
                            BeanUtils.setProperty(object, property, getObject(rs, i, type)) ;
                        }
                    }
                }
                
            }
            /*
            for (Field field : clazz.getDeclaredFields()) {
                String columnName = field.getName() ;

                Object value = rs.getObject(columnName) ;
                
                if (value != null) {
                    BeanUtils.setProperty(object, columnName, value) ;
                }
                
            }*/
            
            result.add(object) ;
       }
        rs.close() ;        
        statement.close() ;
        
        return result ;
    }
    
    private int getType(ResultSetMetaData meta, int i) throws Exception {
    	int colType = meta.getColumnType(i) ;
    	if (colType == Types.NUMERIC) {
        	if (meta.getScale(i) > 0) {
                return Types.DOUBLE;
        	} else {
        		int precision = meta.getPrecision(i) ;
        		if (precision <= 3) {
                    return Types.SMALLINT;
        		} else if (precision <= 10) {
                    return Types.INTEGER;
        		} else {
                    return Types.BIGINT;
        		}
        	}
        } else if (colType == Types.VARCHAR || colType == Types.CHAR ) {
            return Types.CHAR;
        } else if (colType == Types.TIMESTAMP ) {
            return colType;
        } else if (colType == Types.DATE ) {
            return colType;
        } else {
            return Types.JAVA_OBJECT;
        }
    }
    
    private Object getObject(ResultSet rs, int i, int type) throws Exception {
    	Object objValue = rs.getObject(i + 1);
		switch (type) {
		    case Types.CHAR: return objValue ;
		    case Types.JAVA_OBJECT: return objValue ;
		    case Types.DOUBLE: return objValue == null?objValue:rs.getDouble(i + 1) ;
		    case Types.INTEGER: return objValue == null?objValue:rs.getInt(i + 1) ;
		    case Types.BIGINT: return objValue == null?objValue:rs.getLong(i + 1) ;
		    case Types.SMALLINT: return objValue == null?objValue:rs.getShort(i + 1) ;
		    case Types.TIMESTAMP: return rs.getTimestamp(i + 1) ;
		    case Types.DATE: return rs.getDate(i + 1) ;
		    default: return objValue ;
		}
    }
    
    /*
    private void setObjectValue(boolean[] timestampIndexs, int i, boolean clazzIsMap) throws Exception {
    	if (timestampIndexs[i]) {
            if (clazzIsMap) {
            	mapObject.put(propertie, rs.getTimestamp(i + 1)) ;
            } else {
                BeanUtils.setProperty(object, propertie, rs.getTimestamp(i + 1)) ;
            }
    	} else {
            if (clazzIsMap) {
            	mapObject.put(propertie, rs.getObject(i + 1)) ;
            } else {
                BeanUtils.setProperty(object, propertie, rs.getObject(i + 1)) ;
            }
    	}
    }*/
    
    public long getTotalCount(String sql, List<Object> params) throws SQLException {
        long result = -1 ;
        //sql = "SELECT COUNT(*) " + removeSelect(sql) ;

        PreparedStatement statement = this.getConnection().prepareStatement(SqlUtils.getCountSql(sql)) ;
        
        for(int i = 0 ; i < params.size() ; i ++) {
            statement.setObject(i + 1, params.get(i)) ;// 索引从1开始
        }
        
        ResultSet rs = statement.executeQuery() ;

        if (rs.next()) {
            result = rs.getLong(1) ;
        }
        rs.close() ;
        statement.close() ;

        return result;
    }
    
    public long getMaxId(String tableName,String idName) throws SQLException {
        long result = -1 ;
        String sql = String.format("SELECT max(%s) FROM %s ", idName, tableName) ;
        
        log.debug("\nget max id sql :" + sql ) ;

        PreparedStatement statement = this.getConnection().prepareStatement(sql) ;
        
        ResultSet rs = statement.executeQuery() ;

        if (rs.next()) {
            result = rs.getLong(1) + 1 ;
        } else {
            result = 1 ;
        }
        rs.close() ;
        statement.close() ;

        log.debug("\n max id is:" + result) ;
        return result;
    }

    public String getLimitString(final String sql, final List<Object> params, int pageIndex, int pageSize) {   

    	//CodeHelper.getInteger(tableName, columnName, id)
    	int type = SqlUtils.DB_TYPE_ORACLE ;
    	if (type == SqlUtils.DB_TYPE_ORACLE) {// oracle
	        params.add(pageIndex * pageSize) ;
	        params.add((pageIndex-1) * pageSize) ;
	        
	        //return new Oracle9Dialect().getLimitString(sql, true) ;
    	} else { // mysql
	        params.add((pageIndex-1) * pageSize) ;
	        params.add(pageSize) ;
	        
	        //return new MySQL5Dialect().getLimitString(sql, true) ;    		
    	}

        return SqlUtils.getLimitString(type, sql) ;
        //return pagingSelect.toString();   
    } 

        
    public Long getIdBySeqence(final String seqence) throws SQLException {
    	String sql = null ;
    	if (seqence == null || "".equals(seqence)) {
    		sql = "common_seq" ;
    	} else {
    		sql = seqence;
    	}
    	
    	sql = "select " + sql + ".nextval as seq from dual" ;
    	
    	log.debug("get seqence:" + sql) ;
    	
    	return ((BigDecimal)this.getRecord(sql, null).get("seq")).longValue() ;
    }

    
    public Map<String, Object> getRecord(final String sql, SQLGenerator.WHERE where) throws SQLException {
    	Map<String, Object> result = null ;

        PreparedStatement statement = this.getConnection().prepareStatement(sql) ;
        
        if (where != null) {
	        for(int i = 0 ; i < where.params.size() ; i ++) {
	            statement.setObject(i + 1, where.params.get(i)) ;// 索引从1开始
	        }
        }
        
        ResultSet rs = statement.executeQuery() ;

        if (rs.next()) {
        	result = new HashMap<String, Object>() ;
        	ResultSetMetaData meta = rs.getMetaData() ;
            int numberOfColumns = meta.getColumnCount();
            for (int i = 1 ; i <= numberOfColumns ; i ++) {
            	result.put(meta.getColumnName(i).toLowerCase(), rs.getObject(i)) ;
            }
        }
        rs.close() ;
        statement.close() ;

        return result;
    }
    
    public ResultSetMetaData getMetaData(String sql) throws Exception {
    	
        PreparedStatement statement = 
        	this.getConnection().prepareStatement(
        			"select * from (" + sql + ") a where 1=0") ;
        
        return statement.executeQuery().getMetaData() ;
    }


    public int executeSQL(String sql, Object[] values) throws SQLException {
        PreparedStatement statement = this.getConnection().prepareStatement(sql) ;        
        
        for( int i = 0 ; i < values.length ; i ++) {
            statement.setObject(i + 1, values[i]) ;// 索引从1开始
        }
        
        int result = statement.executeUpdate() ;
    
        statement.close() ;
        
        return result ;
    } 




    public int executeSQL(String sql, Object criterion) throws Exception {
        //SQLGenerator generator = new SQLGenerator() ;
    	SQLGenerator generator = generatorAdapter.getInstance(criterion) ;
        
        SQLGenerator.UPDATE update = new SQLGenerator.UPDATE() ;
        update.sql.append(sql) ;
        
        return this.update(update, generator.getWhere()) ;
    } 
    
    


    public int executeName(String name, Object value) throws Exception {
    	
		SqlParser parser = paserSQL(name, value);

		String sql = parser.parse() ;
		List<Object> values = parser.getParams() ;
		
		logSQL(sql, values) ;
		
        PreparedStatement statement = this.getConnection().prepareStatement(sql) ;        
        
        for( int i = 0 ; i < values.size() ; i ++) {
            statement.setObject(i + 1, values.get(i)) ;// 索引从1开始
        }
        
        int result = statement.executeUpdate() ;
    
        statement.close() ;
        
        return result ;
    }

	private SqlParser paserSQL(String name, Object value) throws Exception {
		String[] names = name.split(":") ;
    	
    	SqlBeanInf sqlBean = (SqlBeanInf)CodeHelper.getAppContext().getBean(names[0]);
    	
    	if (sqlBean == null) {
    		throw new Exception("not SQLs's bean found!") ;
    	}
    	String sql = sqlBean.get(names[1]);

    	SqlParser parser = new SqlParser(sql, value) ;
		return parser;
	} 
    
	public ArrayPageList<?> queryName(String name, Object value, Class<?> resultClazz) throws Exception {
		return queryName(name, value, resultClazz, null, null) ;
	}


    public ArrayPageList<?> queryName(
    		String name, Object value, Class<?> resultClazz, Object criterion, ArrayOrderList orderList) throws Exception {
    	
		SqlParser parser = paserSQL(name, value);

		String sql = parser.parse() ;
		List<Object> params = parser.getParams() ;
		
		Integer index = null ;
		Integer size = null ;
		if (value instanceof BaseSO) {
			index = ((BaseSO)value).getPageIndex() ;
			size = ((BaseSO)value).getPageSize() ;
		}
		
		if (criterion != null) {
			//SQLGenerator g = new SQLGenerator() ;
			SQLGenerator generator = generatorAdapter.getInstance(criterion) ;
			SQLGenerator.WHERE w = generator.getWhere() ;
			sql += w.sql ;
			if (orderList != null) {
				sql += orderList.toOrderString() ;
			}
			params.addAll(w.params) ;
		}
		
        return query(sql, params, resultClazz, index, size, false) ;
    } 

    public Object findName(String name, Object value, Class<?> resultClazz) throws Exception {
    	ArrayPageList<?> list = queryName(name, value, resultClazz) ;
    	
    	if (list.size() > 0) {
    		return list.get(0) ;
    	}
    	
        return null ;
    } 
    

    public Long getName(String name, Object value, Long defaultValue) throws Exception {
    	
		SqlParser parser = paserSQL(name, value);

		String sql = parser.parse() ;
		List<Object> params = parser.getParams() ;
		
        long result = defaultValue ;

        PreparedStatement statement = this.getConnection().prepareStatement(sql) ;
        
        for(int i = 0 ; i < params.size() ; i ++) {
            statement.setObject(i + 1, params.get(i)) ;// 索引从1开始
        }
        
        ResultSet rs = statement.executeQuery() ;

        if (rs.next()) {
            result = rs.getLong(1) ;
        }
        rs.close() ;
        statement.close() ;

        return result;
    } 
    
    private void logSQL(String sql, List<Object> params) {
    	if (log.isDebugEnabled()) {
    		log.debug(sql) ;
    		String paramString = "[" ;
    		for (Object param:params) {
    			if (paramString != "[") {
    				paramString += ", " ;
    			}
    			paramString += param ;
    		}
    		
    		log.debug(paramString + "]") ;
    	}
    	
    }
}
